const mysql = require('mysql2/promise');

async function createTimestampTriggers() {
    const connection = await mysql.createConnection({
        host: process.env.DB_HOST || '43.143.253.188',
        user: process.env.DB_USER || 'nextjs',
        password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
        database: process.env.DB_NAME || 'nextjs'
    });

    try {
        console.log('🔧 为MySQL 5.5数据库创建自动时间戳触发器...');

        // 获取所有表名
        const [tables] = await connection.execute('SHOW TABLES');
        console.log(`📋 找到 ${tables.length} 个表`);

        for (const table of tables) {
            const tableName = Object.values(table)[0];
            console.log(`\n🔍 处理表: ${tableName}`);

            // 检查表是否有created_at和updated_at字段
            const [columns] = await connection.execute(`SHOW COLUMNS FROM ${tableName}`);
            const hasCreatedAt = columns.some(col => col.Field === 'created_at');
            const hasUpdatedAt = columns.some(col => col.Field === 'updated_at');

            if (!hasCreatedAt && !hasUpdatedAt) {
                console.log(`⏭️ 跳过表 ${tableName}（没有时间戳字段）`);
                continue;
            }

            // 删除现有的触发器（如果存在）
            try {
                await connection.execute(`DROP TRIGGER IF EXISTS ${tableName}_created_at_trigger`);
                await connection.execute(`DROP TRIGGER IF EXISTS ${tableName}_updated_at_trigger`);
            } catch (error) {
                // 忽略删除不存在的触发器的错误
            }

            // 创建INSERT触发器（设置created_at）
            if (hasCreatedAt) {
                const insertTrigger = `
          CREATE TRIGGER ${tableName}_created_at_trigger
          BEFORE INSERT ON ${tableName}
          FOR EACH ROW
          BEGIN
            IF NEW.created_at IS NULL THEN
              SET NEW.created_at = NOW();
            END IF;
          END
        `;

                try {
                    await connection.execute(insertTrigger);
                    console.log(`✅ 创建INSERT触发器: ${tableName}_created_at_trigger`);
                } catch (error) {
                    console.log(`❌ 创建INSERT触发器失败: ${error.message}`);
                }
            }

            // 创建UPDATE触发器（设置updated_at）
            if (hasUpdatedAt) {
                const updateTrigger = `
          CREATE TRIGGER ${tableName}_updated_at_trigger
          BEFORE UPDATE ON ${tableName}
          FOR EACH ROW
          BEGIN
            SET NEW.updated_at = NOW();
          END
        `;

                try {
                    await connection.execute(updateTrigger);
                    console.log(`✅ 创建UPDATE触发器: ${tableName}_updated_at_trigger`);
                } catch (error) {
                    console.log(`❌ 创建UPDATE触发器失败: ${error.message}`);
                }
            }
        }

        console.log('\n🎉 所有时间戳触发器创建完成！');

        // 显示创建的触发器列表
        const [triggers] = await connection.execute('SHOW TRIGGERS');
        console.log(`\n📋 总共创建了 ${triggers.length} 个触发器:`);
        triggers.forEach(trigger => {
            console.log(`  - ${trigger.Trigger} (${trigger.Event} on ${trigger.Table})`);
        });

    } catch (error) {
        console.error('❌ 创建触发器时出错:', error);
    } finally {
        await connection.end();
    }
}

// 运行脚本
createTimestampTriggers().catch(console.error);
